import sqlite3
from datetime import datetime, timedelta

from models.task_models import Task, TaskStatus, TaskPriority


class TaskDatabase:
    def __init__(self, db_path="tasks.db"):
        self.db_path = db_path
        self._create_tables()

    def _create_tables(self):
        """创建数据库表"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS tasks (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    title TEXT NOT NULL,
                    description TEXT,
                    due_date DATE NOT NULL,
                    interval_days INTEGER DEFAULT 1,
                    priority TEXT NOT NULL,
                    status TEXT NOT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    completed_at DATETIME,
                    attachments TEXT,
                    is_reminded BOOLEAN DEFAULT 0,
                    last_reminded DATETIME 
                )
            ''')
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS reminders (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    task_id INTEGER NOT NULL,
                    reminder_date DATE NOT NULL,
                    reminded BOOLEAN DEFAULT 0,
                    FOREIGN KEY (task_id) REFERENCES tasks(id)
                )
            ''')
            conn.commit()

    def _get_connection(self):
        """获取数据库连接"""
        return sqlite3.connect(self.db_path)

    def add_task(self, task):
        """添加新任务"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                INSERT INTO tasks (title, description, due_date, interval_days, priority, status, attachments,is_reminded, last_reminded)
                VALUES (?, ?, ?, ?, ?, ?, ?,?,?)
            ''', (
                task.title,
                task.description,
                task.due_date.strftime("%Y-%m-%d"),
                task.interval_days,
                task.priority.value,
                task.status.value,
                ",".join(task.attachments) if task.attachments else None,
                task.is_reminded,
                task.last_reminded.strftime("%Y-%m-%d") if task.last_reminded else None,
            ))
            task.id = cursor.lastrowid
            conn.commit()

            # 添加提醒记录
            for reminder_date in task.calculate_reminders():
                cursor.execute('''
                    INSERT INTO reminders (task_id, reminder_date,reminded)
                    VALUES (?, ?, ?)
                ''', (task.id, reminder_date.strftime("%Y-%m-%d"), False))

            conn.commit()
        return task

    def update_task(self, task):
        """更新任务"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                UPDATE tasks
                SET title = ?, description = ?, due_date = ?, interval_days = ?, 
                    priority = ?, status = ?, completed_at = ?, attachments = ?
                WHERE id = ?
            ''', (
                task.title,
                task.description,
                task.due_date.strftime("%Y-%m-%d"),
                task.interval_days,
                task.priority.value,
                task.status.value,
                task.completed_at.strftime("%Y-%m-%d %H:%M:%S") if task.completed_at else None,
                ",".join(task.attachments) if task.attachments else None,
                task.id
            ))

            # 删除旧提醒并添加新提醒
            cursor.execute("DELETE FROM reminders WHERE task_id = ?", (task.id,))
            for reminder_date in task.calculate_reminders():
                cursor.execute('''
                    INSERT INTO reminders (task_id, reminder_date)
                    VALUES (?, ?)
                ''', (task.id, reminder_date.strftime("%Y-%m-%d")))

            conn.commit()

    def delete_task(self, task_id):
        """删除任务"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM tasks WHERE id = ?", (task_id,))
            cursor.execute("DELETE FROM reminders WHERE task_id = ?", (task_id,))
            conn.commit()

    def get_task(self, task_id):
        """获取单个任务"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM tasks WHERE id = ?", (task_id,))
            row = cursor.fetchone()
            if row:
                return self._row_to_task(row)
        return None

    def get_all_tasks(self, include_expired=True):
        """获取所有任务"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            if include_expired:
                cursor.execute("SELECT * FROM tasks ORDER BY due_date ASC")
            else:
                cursor.execute("SELECT * FROM tasks WHERE status != ? ORDER BY due_date ASC",
                               (TaskStatus.EXPIRED.value,))
            return [self._row_to_task(row) for row in cursor.fetchall()]

    def get_tasks_with_filter(self, status=None, date_filter="三天内", page=1, per_page=10):
        """获取带筛选条件和分页的任务列表"""
        today = datetime.now().date()
        offset = (page - 1) * per_page

        # 构建查询条件和参数
        conditions = []
        params = []

        # 状态筛选
        if status == "uncompleted":
            conditions.append("status != ?")
            params.append(TaskStatus.COMPLETED.value)
        elif status:
            conditions.append("status = ?")
            params.append(status)

        # 日期筛选
        if date_filter == "今天":
            conditions.append("due_date = ?")
            params.append(today.strftime("%Y-%m-%d"))
        elif date_filter == "三天内":
            three_days_later = today + timedelta(days=3)
            conditions.append("due_date BETWEEN ? AND ?")
            params.extend([today.strftime("%Y-%m-%d"), three_days_later.strftime("%Y-%m-%d")])
        elif date_filter == "一周内":
            one_week_later = today + timedelta(days=7)
            conditions.append("due_date BETWEEN ? AND ?")
            params.extend([today.strftime("%Y-%m-%d"), one_week_later.strftime("%Y-%m-%d")])
        elif date_filter == "一个月内":
            one_month_later = today + timedelta(days=30)
            conditions.append("due_date BETWEEN ? AND ?")
            params.extend([today.strftime("%Y-%m-%d"), one_month_later.strftime("%Y-%m-%d")])

        # 构建查询语句
        where_clause = " AND ".join(conditions) if conditions else "1=1"

        # 查询任务数据
        with self._get_connection() as conn:
            cursor = conn.cursor()

            # 获取总数
            count_sql = f"SELECT COUNT(*) FROM tasks WHERE {where_clause}"
            cursor.execute(count_sql, params)
            total_count = cursor.fetchone()[0]

            # 获取分页数据
            query_sql = f"""
                SELECT * FROM tasks 
                WHERE {where_clause}
                ORDER BY 
                    CASE 
                        WHEN due_date < ? THEN 0 
                        WHEN due_date = ? THEN 1
                        ELSE 2 
                    END,
                    CASE priority
                        WHEN 'HIGH' THEN 1
                        WHEN 'MEDIUM' THEN 2
                        WHEN 'LOW' THEN 3
                    END,
                    due_date ASC
                LIMIT ? OFFSET ?
            """

            # 添加排序参数
            sort_params = [
                today.strftime("%Y-%m-%d"),  # 已过期
                today.strftime("%Y-%m-%d"),  # 今天到期
            ]

            cursor.execute(query_sql, params + sort_params + [per_page, offset])
            rows = cursor.fetchall()

        tasks = [self._row_to_task(row) for row in rows]
        return tasks, total_count

    def get_tasks_by_status(self, status):
        """按状态获取任务"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM tasks WHERE status = ? ORDER BY due_date ASC", (status.value,))
            return [self._row_to_task(row) for row in cursor.fetchall()]

    def get_due_tasks(self):
        """获取今天到期的任务"""
        today = datetime.now().date().strftime("%Y-%m-%d")
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT * FROM tasks WHERE due_date = ? AND status != ?",
                           (today, TaskStatus.COMPLETED.value))
            return [self._row_to_task(row) for row in cursor.fetchall()]

    def get_today_reminders(self):
        """获取今天需要提醒的任务"""
        today = datetime.now().date().strftime("%Y-%m-%d")
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                SELECT t.* 
                FROM tasks t
                JOIN reminders r ON t.id = r.task_id
                WHERE r.reminder_date = ? AND r.reminded = 0 and t.status != ?
            ''', (today, TaskStatus.COMPLETED.value))
            return [self._row_to_task(row) for row in cursor.fetchall()]

    def mark_reminder_done(self, task_id):
        """标记提醒为已完成"""
        today = datetime.now().date().strftime("%Y-%m-%d")
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''
                UPDATE reminders
                SET reminded = 1
                WHERE task_id = ? AND reminder_date = ?
            ''', (task_id, today))
            cursor.execute('''
               UPDATE tasks
               SET is_reminded = 1, last_reminded = ?
               WHERE id = ?
          ''', (today, task_id))
            conn.commit()

    def _row_to_task(self, row):
        """将数据库行转换为Task对象"""
        task = Task(
            title=row[1],
            description=row[2],
            due_date=datetime.strptime(row[3], "%Y-%m-%d").date(),
            interval_days=row[4],
            priority=TaskPriority(row[5]),
        )
        task.id = row[0]
        task.status = TaskStatus(row[6])
        task.created_at = datetime.strptime(row[7], "%Y-%m-%d %H:%M:%S")
        task.completed_at = datetime.strptime(row[8], "%Y-%m-%d %H:%M:%S") if row[8] else None
        task.attachments = row[9].split(",") if row[9] else []
        task.is_reminded = bool(row[10]) if row[10] else False
        task.last_reminded = datetime.strptime(row[11], "%Y-%m-%d %H:%M:%S") if row[11] else None
        return task
